﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
using System.Web;
namespace Enring.Data
{
    /// <summary>
    /// Property Field Type 
    /// </summary>
    public enum PropertyType
    {
        String,
        Int,
        DataTime,
        DetailTable,
        RelatedFile,
        Identity,
        Unknown
    }

    public enum DatabaseType
    {
        SQLServer,
        Oracle,
        MySQL,
        Sqlite
    }

    /// <summary>
    /// CRUD
    /// </summary>
    public enum SqlStatementType
    {
        Select,
        Insert,
        Update,
        Delete
    }


    /// <summary>
    /// DataMapping class is used to import or export data to the business layer
    /// </summary>
    public class DataMapping
    {
        private HttpContextBase context;
        private ViewDataDictionary viewData;
        public DataMapping(HttpContextBase h)
        {
            context = h;
            Init();
        }

        public DataMapping(HttpContextBase h,ViewDataDictionary data)
        {
            context = h;
            viewData = data;
            Init();
        }

        private void Init()
        {
            ConnectionKey = Const.DefaultConnectionStringKey;
            DatabaseType = Data.DatabaseType.SQLServer;
        }

        #region Get Data

        /// <summary>
        /// Parse SqlParameters from configration
        /// </summary>
        /// <param name="sqlNode">Node include parameters</param>
        /// <returns></returns>
        protected SqlParameter[] ParseParameters(XmlNode sqlNode)
        {
            XmlNodeList parameterNodes = sqlNode.SelectNodes(".//Parameters/Parameter");
            SqlParameter[] parameters = new SqlParameter[parameterNodes.Count];
            int i = 0;
            foreach (XmlNode parameterNode in parameterNodes)
            {
                parameters[i] = ParseParameter(parameterNode);
                i++;
            }
            return parameters;
        }

        /// <summary>
        /// Parse parameter from configration
        /// </summary>
        /// <param name="parameterNode"></param>
        /// <returns></returns>
        protected SqlParameter ParseParameter(XmlNode parameterNode)
        {
            SqlParameter p = new SqlParameter();
            string source = XmlHelper.GetAttributeValue(parameterNode, "source").ToLower().Trim();

            if (source.Equals("request"))
            {
                string key = XmlHelper.GetAttributeValue(parameterNode, "key").ToLower().Trim();
                string value = context.Request[key];
                p.ParameterName = string.Format("@{0}", XmlHelper.GetAttributeValue(parameterNode, "name"));
                p.Value = value;
            }
            else if (source.Equals("form"))
            {
                string key = Const.FieldPrefix + XmlHelper.GetAttributeValue(parameterNode, "key").ToLower().Trim();
                string value = context.Request[key];
                p.ParameterName = string.Format("@{0}", XmlHelper.GetAttributeValue(parameterNode, "name"));
                p.Value = value;
            }
            return p;
        }

        #endregion   

        #region Retrieve Data
        /// <summary>
        /// Get dataset form data configration node.
        /// </summary>
        /// <param name="config"></param>
        /// <returns></returns>
        public DataSet GetDataFromConfigration(XmlNode config)
        {
            if (!config.Name.ToLower().Equals("datasource"))
                throw new CustomerException("the configration node name must be DataSource");

            string connString = DB.GetConnectionString(GetDatabaseConnectionString(config));
            XmlNode selectNode = config.SelectSingleNode("Select");
            if (selectNode == null)
                return new DataSet();
            string sql = XmlHelper.GetNodeValue(selectNode.SelectSingleNode("Text"));
            string type = XmlHelper.GetAttributeValue(selectNode, "type", "text").ToLower().Trim();
            CommandType cmdType = CommandType.Text;
            if(type.Equals("text"))
                cmdType = CommandType.Text;
            else if(type.Equals("procedure"))
                cmdType = CommandType.StoredProcedure;
            else if(type.Equals("table"))
                cmdType = CommandType.TableDirect;
            
            //Prepare parameters
            SqlParameter[] parameters = ParseParameters(config.SelectSingleNode("Select"));
            
            return DB.GetDataSet(connString,cmdType,sql,parameters);
        }

        /// <summary>
        /// Save Dataset to viewData
        /// </summary>
        /// <param name="dataSourceNode"></param>
        /// <param name="viewData"></param>
        public DataSet SetDataset(XmlNode dataSourceNode,ViewDataDictionary viewData)
        {
            string name = XmlHelper.GetAttributeValue(dataSourceNode, "name");
            if (viewData[name] != null)
                viewData.Remove(name);
            DataSet ds = GetDataFromConfigration(dataSourceNode);
            if (ds == null) return new DataSet();
            if (name.Equals(Const.DefaultDataSetKey))
                throw new CustomerException("DataSource node can't use the name of 'data'. Please check your configration and try again.");
            viewData.Add(name, ds);
            if (XmlHelper.GetAttributeValue(dataSourceNode, "default").Equals("true"))
            {
                if (viewData.Keys.Contains(Const.DefaultDataSetKey))
                    viewData.Remove(Const.DefaultDataSetKey);
                viewData.Add(Const.DefaultDataSetKey, ds);
            }
            return ds;
        }

        #endregion

        #region Update Data

        /// <summary>
        /// Insert or update data from a request
        /// </summary>
        public void ProgressRequest()
        {
            if (context.Request[Const.SubmitActionType] == null) return;
            string formName = context.Request[Const.SubmitFormName].Trim();
            string formGUID = context.Request[Const.SubmitFormGUID].Trim();

            XmlDocument configDom = viewData[Const.DefaultConfigKey] as XmlDocument;            
            XmlNode formNode = configDom.SelectSingleNode(string.Format(".//Form[@guid='{0}']",formGUID));
            if (formNode == null)
                throw new CustomerException(string.Format("Can't find the form with name {1} - guid {0}",formGUID,formName));
            
            XmlNode dataNode = formNode.SelectSingleNode(".//Data");
            if (dataNode == null)
                throw new CustomerException(string.Format("Can't find the data node in form with name {1} - guid {0}", formGUID, formName));

            //Update data
            SaveDataForm(dataNode);    

        }

        /// <summary>
        /// Update data form values
        /// </summary>
        /// <param name="values"></param>
        /// <param name="dataNode">Data node</param>
        public void SaveDataForm(XmlNode dataNode)
        {
            bool isInsert = context.Request[Const.SubmitActionType].ToLower().Trim().Equals("insert");
            Dictionary<string, string> values = new Dictionary<string, string>();
            XmlNode dataSourceNode = dataNode.SelectSingleNode("DataSource");

            //Check if there's an InsertNode
            XmlNode updateNode = dataNode.SelectSingleNode(string.Format("DataSource/{0}", isInsert ? "Insert" : "Update"));

            if (dataSourceNode.Attributes["ref"] != null)
            {
                if (updateNode != null)
                    UpdateObjectByAdapter(isInsert, dataSourceNode, updateNode);
                else
                {
                    //Get the refernce object
                    string refer = XmlHelper.GetAttributeValue(dataSourceNode, "ref");
                    XmlDocument dataDom = XmlHelper.GetConfigration(refer);
                    if (dataDom != null)
                    {
                        string objectid = XmlHelper.GetAttributeValue(dataSourceNode, "object");
                        XmlNode objectNode = dataDom.SelectSingleNode(string.Format(".//Object[@id='{0}']", objectid));
                        if (objectNode != null)
                        {
                            //Update from object Model
                            UpdateObjectByDataMapping(isInsert, objectNode);
                        }
                        else
                        {
                            throw new CustomerException(string.Format("No DataMapping object found ref:{0} objct:{1}", refer, objectid));
                        }
                    }
                    else
                        throw new CustomerException("No DataMapping Configration object found ref:{0}", refer);
                }                
            }
            else
            {                   
                //Read the text configration
                UpdateObjectByAdapter(isInsert, dataSourceNode, updateNode);
            }
        }

        /// <summary>
        /// Save data from DataAdapter configration
        /// </summary>
        /// <param name="isInsert"></param>
        /// <param name="dataSourceNode"></param>
        /// <param name="updateNode"></param>
        private void UpdateObjectByAdapter(bool isInsert, XmlNode dataSourceNode, XmlNode updateNode)
        {
            if (isInsert)
            {
                InsertObject(dataSourceNode, updateNode);
            }
            else
            {
                //Update data
                UpdateObject(dataSourceNode, updateNode);
            }
        }

        /// <summary>
        /// Save data from DataMapping object
        /// </summary>
        /// <param name="node"></param>
        private int UpdateObjectByDataMapping(bool isInsert,XmlNode node)
        {
            if (node == null)
                throw new CustomerException("Not Update configration found.");
            //Get the obejct configration
            Dictionary<string, string> values = new Dictionary<string, string>();
            string sql = GetSql(isInsert ? SqlStatementType.Insert : SqlStatementType.Update, node,values);

            string connectionKey = XmlHelper.GetAttributeValue(node, "connection",Const.DefaultConnectionStringKey);
            int result = DB.ExecuteNonQuery(DB.GetConnectionString(connectionKey), CommandType.Text, sql, null);

            ////临时代码，用来记录数据变化，实现工作流程类似功能
            _RecordChange(values,0);
            return result;
        }

        public void _RecordChange(Dictionary<string, string> values,int status)
        {
            if (values.ContainsKey("table") && values.ContainsKey("field") && values.ContainsKey("value") && values.ContainsKey("user"))
            {
                if (values["table"].Equals("TSX_BIZDATA_YXJL"))
                {
                    string checkExsitsSql = string.Format("SELECT * FROM TSX_RECORDCHANGE WHERE [TABLE] = '{0}' AND IDVALUE = '{1}'",values["table"],values["value"]);
                    DataSet dsRecordChange = DB.GetDataSet(checkExsitsSql);
                    if (DB.HasData(dsRecordChange))
                    {
                        //Update a existing record.
                        string sql =string.Format( "UPDATE TSX_RECORDCHANGE SET Status = {1} WHERE IDVALUE = '{0}'",
                            values["value"],
                            status);
                        DB.ExecuteNonQuery(sql);
                    }
                    else
                    {
                        //Insert a new record.
                        string sql = string.Format("INSERT INTO TSX_RECORDCHANGE ([TABLE],IDFIELD,IDVALUE,OPERATOR_ID,CHANGETIME,TYPE,STATUS) VALUES ('{0}','{1}','{2}','{3}','{4}',{5},{6})",
                            values["table"],
                            values["field"],
                            values["value"],
                            values["user"],
                            DateTime.Now.ToString(),
                            0,
                            status);
                        DB.ExecuteNonQuery(sql);
                    }
                }
            }
        }


        public string GetSql(SqlStatementType sqlType, XmlNode objectNode)
        {
            return GetSql(sqlType, objectNode,null);
        }

        /// <summary>
        /// Get sql from object mode
        /// </summary>
        /// <param name="sqlType"></param>
        /// <param name="objectNode"></param>
        /// <returns></returns>
        public string GetSql(SqlStatementType sqlType, XmlNode objectNode,Dictionary<string,string> values)
        {
            StringBuilder sbSql = new StringBuilder();
            
            string table = XmlHelper.GetAttributeValue(objectNode,"table");

            ////
            if (values != null)
            {
                values.Add("table", table);
            }

            string parentField = XmlHelper.GetAttributeValue(objectNode, "parent_key_field");

            XmlNodeList properties = objectNode.SelectNodes("Property");
            if (properties.Count == 0) return string.Empty;            

            if (sqlType == SqlStatementType.Delete)
            {
                //Deleete SQL
                string dataid = viewData["dataid"].ToString();
                sbSql.AppendFormat("DELETE FROM {0} WHERE {1}", table, GetWhereClause(objectNode,dataid));
            }
            else if (sqlType == SqlStatementType.Insert)
            {
                //Insert SQL
                string cmdText = "INSERT INTO {0} ({1}) VALUES ({2})";
                
                StringBuilder sbKeys = new StringBuilder();
                StringBuilder sbValues = new StringBuilder();
                foreach (XmlNode p in properties)
                {
                    string pType = XmlHelper.GetAttributeValue(p,"type");
                    //Only normal data type accepted
                    if (IsObjectProperty(pType))
                        continue;

                    string pName = XmlHelper.GetAttributeValue(p, "field");
                    string pValue = string.Empty;

                    //Check the type
                    if (XmlHelper.GetAttributeValue(p,"key").Equals("true") 
                        && XmlHelper.GetAttributeValue(p, "key_auto").Equals("true"))                    
                    {
                        //Key column
                        string keyType = XmlHelper.GetAttributeValue(p, "key_type");
                        if (keyType.Equals("guid"))
                        {
                            sbKeys.AppendFormat("{0},",pName);
                            string guid = Guid.NewGuid().ToString("B");
                            sbValues.Append(GetKeyValue(pType, guid));
                            ////
                            if (values != null)
                            {
                                values.Add("field", pName);
                                values.Add("value", guid);
                            }
                        }
                    }
                    else if (XmlHelper.GetAttributeValue(p, "calc").Equals("true"))
                    {
                        pValue = GetCalcFieldValue(p, pValue);
                        //Insert value
                        if (pValue != string.Empty)
                        {
                            sbKeys.AppendFormat("{0},", pName);
                            sbValues.Append(GetKeyValue(pType, pValue));
                        }
                    }
                    else
                    {
                        pValue = GetProertyValue(pName, parentField);

                        if (pValue != string.Empty)
                        {
                            sbKeys.AppendFormat("{0},", pName);
                            sbValues.Append(GetKeyValue(pType, pValue));
                        }
                        else
                        {
                            //No value found. Leave it as dbnull
                        }
                        ////
                        if (pName.Equals("OPERATOR_GUID"))
                        {
                            if (values != null)
                            {
                                values.Add("user", pValue);
                            }
                        }
                    }
                    //End loop
                }
                //Generate Sql
                sbSql.AppendFormat(cmdText, 
                    table, 
                    sbKeys.ToString().TrimEnd(",".ToCharArray()), 
                    sbValues.ToString().TrimEnd(",".ToCharArray()));
            
            }
            else if (sqlType == SqlStatementType.Update)
            {
                //Update SQL
                string cmdText = "UPDATE {0} SET {1} WHERE {2}";
                string where = string.Empty;

                StringBuilder sbValues = new StringBuilder();
                foreach (XmlNode p in properties)
                {
                    string pType = XmlHelper.GetAttributeValue(p, "type");
                    //Only normal data type accepted
                    if (IsObjectProperty(pType))
                        continue;

                    string pName = XmlHelper.GetAttributeValue(p, "field");
                    string pValue = string.Empty;
                    
                    //Check the type
                    if (XmlHelper.GetAttributeValue(p, "key").Equals("true")
                        && XmlHelper.GetAttributeValue(p, "key_auto").Equals("true"))
                    {
                        where = GetWhereClause(objectNode);
                        ////
                        if (values != null)
                        {
                            string guid = context.Request[Const.FieldPrefix + Const.DefaultDataID].ToString();
                            values.Add("field", pName);
                            values.Add("value", guid);
                        }
                    }
                    else if (XmlHelper.GetAttributeValue(p, "calc").Equals("true"))
                    {
                        string needUpdate = XmlHelper.GetAttributeValue(p, "update", "true");
                        if (needUpdate.Equals("true"))
                        {
                            pValue = GetCalcFieldValue(p, pValue);

                            ////为了满足流程要求而修改
                            if (table.Equals("TSX_BIZDATA_YXJL") && pName.Equals("STATUS"))
                            {
                                pValue = "3";
                            }
                        

                            //Update value
                            if (pValue != string.Empty)
                            {
                                sbValues.AppendFormat("{0}={1}", pName, GetKeyValue(pType, pValue));
                            }
                            else
                            {
                                //No value found. Leave it as dbnull
                            }
                        }
                    }
                    else
                    {
                        pValue = GetProertyValue(pName, parentField);

                        
                        if (pValue != string.Empty)
                        {
                            sbValues.AppendFormat("{0}={1}",pName, GetKeyValue(pType, pValue));
                        }
                        else
                        {
                            //No value found. Leave it as dbnull
                        }
                        ////
                        if (pName.Equals("OPERATOR_GUID"))
                        {
                            if (values != null)
                            {
                                values.Add("user", pValue);
                            }
                        }
                        
                    }
                    //End loop
                }
                //Generate Sql
                sbSql.AppendFormat(cmdText,
                    table,
                    sbValues.ToString().TrimEnd(",".ToCharArray()),
                    where);
            }
            else
            {
                //Select SQL
                string cmdText = "SELECT {1} FROM {0}";
                string where = GetWhereClause(objectNode);

                StringBuilder sbValues = new StringBuilder();
                foreach (XmlNode p in properties)
                {
                    string pType = XmlHelper.GetAttributeValue(p, "type");
                    //Only normal data type accepted
                    if (IsObjectProperty(pType))
                        continue;
                    string pName = XmlHelper.GetAttributeValue(p, "field");
                    
                    sbValues.AppendFormat("{0},", pName);
                    //End loop
                }
                //Generate Sql
                sbSql.AppendFormat(cmdText,
                    table,
                    sbValues.ToString().TrimEnd(",".ToCharArray()));
                if (!string.IsNullOrEmpty(where))
                    sbSql.AppendFormat(" WHERE {0}",where);
            }
            
            return sbSql.ToString();
        }

        private string GetCalcFieldValue(XmlNode p, string pValue)
        {
            //Calculate columns
            string calSource = XmlHelper.GetAttributeValue(p, "source").ToLower().Trim();
            string calSourceKey = XmlHelper.GetAttributeValue(p, "source_key").ToLower().Trim();

            if (calSource.Equals("value"))
            {
                pValue = XmlHelper.GetAttributeValue(p, "source_value");
            }
            else if (calSource.Equals("user_guid"))
            {
                pValue = EnringContext.Current.CurrentUserID;
            }
            else if (calSource.Equals("time"))
            {
                pValue = DateTime.Now.ToString();
            }
            else if (calSource.Equals("session") && calSourceKey != null)
            {
                pValue = context.Session[calSourceKey].ToString();
            }
            else if (calSource.Equals("application") && calSourceKey != null)
            {
                pValue = context.Application[calSourceKey].ToString();
            }
            else if (calSource.Equals("request") && calSourceKey != null)
            {
                pValue = context.Request[calSourceKey].ToString();
            }
            else if (calSource.Equals("cookie") && calSourceKey != null)
            {
                pValue = context.Request.Cookies[calSourceKey].Value;
            }
            return pValue;
        }

        private static bool IsObjectProperty(string pType)
        {
            return pType.Equals("object") || pType.Equals("RelatedFile");
        }

        /// <summary>
        /// Get field value from request or viewdata
        /// </summary>
        /// <param name="pName"></param>
        /// <param name="parentFieldName"></param>
        /// <returns></returns>
        private string GetProertyValue(string pName, string parentFieldName)
        {
            string pValue = string.Empty;
            if (context.Request[Const.FieldPrefix + pName] != null)
                pValue = context.Request[Const.FieldPrefix + pName];
            
            //Get the parent data from 'dataid' field in viewData
            if(string.IsNullOrEmpty(pValue) && pName.Equals(parentFieldName))
            {
                if (CheckViewData(Const.DefaultDataID))
                    pValue = viewData[Const.DefaultDataID].ToString();
                
            }
            return pValue;
        }

        /// <summary>
        /// Get object ID Clause for select,update,delete
        /// </summary>
        /// <param name="objectNode"></param>
        /// <param name="value">Parameter Value</param>
        /// <returns></returns>
        private string GetWhereClause(XmlNode objectNode,string value)
        {
            XmlNodeList keyPropertyNodes = objectNode.SelectNodes("Property[@key='true']");
            StringBuilder keys = new StringBuilder();
            if (keyPropertyNodes.Count == 1)
            {
                XmlNode keyProperty = keyPropertyNodes[0];
                keys.AppendFormat("{0}={1}",
                    XmlHelper.GetAttributeValue(keyProperty, "field"),
                    GetKeyValue(keyProperty,value,""));                
            }
            if (keys.Length > 0)
            {
                keys.Insert(0, "(");
                keys.Append(")");
            }
            return keys.ToString();
        }

        /// <summary>
        /// Get object ID Clause for select,update,delete
        /// </summary>
        /// <example>
        /// (ID = 'XXX-XXX-XXX')
        /// </example>
        /// <param name="objectNode"></param>
        /// <returns></returns>
        private string GetWhereClause(XmlNode objectNode)
        {
            XmlNodeList keyPropertyNodes = objectNode.SelectNodes("Property[@key='true']");
            StringBuilder keys = new StringBuilder();
            if (keyPropertyNodes.Count == 1)
            {
                XmlNode keyProperty = keyPropertyNodes[0];
                if (CheckRequestData(Const.FieldPrefix + Const.DefaultDataID))
                {
                    keys.AppendFormat("{0}={1}",
                        XmlHelper.GetAttributeValue(keyProperty, "field"),
                        GetKeyValue(keyProperty, context.Request[Const.FieldPrefix + Const.DefaultDataID].ToString(), ""));
                }
            }
            else
            {
                //Multi keys:The viewData must has key with the format of ""
                foreach (XmlNode keyProperty in keyPropertyNodes)
                {
                    if (CheckRequestData(Const.FieldPrefix + XmlHelper.GetAttributeValue(keyProperty, "field")))
                    {
                        if (keys.Length > 0)
                            keys.Append(" AND ");
                        keys.AppendFormat("{0}={1}", XmlHelper.GetAttributeValue(keyProperty, "field"),
                            GetKeyValue(keyProperty, context.Request[Const.FieldPrefix + XmlHelper.GetAttributeValue(keyProperty, "field")].ToString(), ""));
                    }                    
                }                
            }
            if (keys.Length > 0)
            {
                keys.Insert(0, "(");
                keys.Append(")");
            }
            return keys.ToString();
        }

        private bool CheckRequestData(string key)
        {
            if (context.Request[key] != null)
            {
                return true;
            }
            else
                throw new CustomerException("Null {0} in Request", key);
        }

        private bool CheckViewData(string key)
        {
            if (viewData[key] != null)
            {
                return true;
            }
            else
                throw new CustomerException("Null {0} in ViewData", key);
        }

        /// <summary>
        /// Update or Insert from DataAdapter node
        /// </summary>
        /// <param name="dataSourceNode"></param>
        /// <param name="node"></param>
        private void UpdateObject(XmlNode dataSourceNode, XmlNode node)
        {
            if (node == null)
                throw new CustomerException("Not Update configration found.");

            CommandType cmdType = ParseCommandType(node);
            string cmdText = XmlHelper.GetNodeValue(node.SelectSingleNode("Text"));
            XmlNodeList parameterNodes = node.SelectNodes("Parameters/Parameter");

            StringBuilder sbValues = new StringBuilder();
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (XmlNode p in parameterNodes)
            {
                string pType;
                string pName;
                string pValue;
                ParseParameter(p, out pType, out pName, out pValue);
                //
                sbValues.Append(string.Format("{0}={1}",pName,GetKeyValue(pType,pValue)));
                //If there's a parameter name in the sql statement then add it to the parameter list
                if (cmdText.IndexOf(string.Format("@{0}", pName)) > -1)
                {
                    parameters.Add(ParseParameter(p));
                }
                //End loop
            }
            //Generate Sql
            cmdText = string.Format(cmdText, sbValues.ToString().TrimEnd(",".ToCharArray()));

            //Execute
            DB.ExecuteNonQuery(DB.GetConnectionString(GetDatabaseConnectionString(dataSourceNode)), cmdType, cmdText, parameters.ToArray());
        }

        private string GetKeyValue(XmlNode propertyNode, string value, string seperator)
        {
            return GetKeyValue(XmlHelper.GetAttributeValue(propertyNode, "type"), value, seperator);
        }

        private string GetKeyValue(XmlNode propertyNode,string value)
        {
            return GetKeyValue(XmlHelper.GetAttributeValue(propertyNode, "type"), value);
        }

        private string GetKeyValue(string pType, string pValue)
        {
            return GetKeyValue(pType, pValue, ",");
        }

        private string GetKeyValue(string pType, string pValue,string seperator)
        {
            if (pValue != string.Empty)
            {
                if (pType.Equals("number"))
                    return string.Format("{0}{1}", pValue,seperator);
                else
                    return string.Format("'{0}'{1}", pValue,seperator);
            }
            else
            {
                return "";
            }
        }

        private void InsertObject(XmlNode dataSourceNode, XmlNode insertNode)
        {
            if (insertNode == null)
                throw new CustomerException("Not Insert configration found.");
            CommandType cmdType = ParseCommandType(insertNode);
            string cmdText = XmlHelper.GetNodeValue(insertNode.SelectSingleNode("Text"));
            XmlNodeList parameterNodes = insertNode.SelectNodes("Parameters/Parameter");

            StringBuilder sbKeys = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            foreach (XmlNode p in parameterNodes)
            {
                string pType;
                string pName;
                string pValue;
                ParseParameter(p, out pType, out pName, out pValue);

                if (pValue != string.Empty)
                {
                    sbKeys.AppendFormat("{0},",pName);
                    sbValues.Append(GetKeyValue(pType, pValue));
                }
                //End loop
            }
            //Generate Sql
            cmdText = string.Format(cmdText, sbKeys.ToString().TrimEnd(",".ToCharArray()), sbValues.ToString().TrimEnd(",".ToCharArray()));
            //Execute
            DB.ExecuteNonQuery(DB.GetConnectionString(GetDatabaseConnectionString(dataSourceNode)), cmdType, cmdText, null);
        }

        private static CommandType ParseCommandType(XmlNode insertNode)
        {
            CommandType cmdType = CommandType.Text;
            string type = XmlHelper.GetAttributeValue(insertNode, "type", "text").ToLower();
            if (type.Equals("text"))
                cmdType = CommandType.Text;
            else if (type.Equals("table"))
                cmdType = CommandType.TableDirect;
            else if (type.Equals("procedure"))
                cmdType = CommandType.StoredProcedure;
            return cmdType;
        }

        /// <summary>
        /// Parse SqlParameter form a prameter node.
        /// </summary>
        /// <param name="p"></param>
        /// <param name="pType"></param>
        /// <param name="pName"></param>
        /// <param name="pValue"></param>
        private void ParseParameter(XmlNode p, out string pType, out string pName, out string pValue)
        {
            pType = XmlHelper.GetAttributeValue(p, "type");
            pName = XmlHelper.GetAttributeValue(p, "name");
            string pSource = XmlHelper.GetAttributeValue(p, "source");
            string pKey = XmlHelper.GetAttributeValue(p, "key");

            pValue = string.Empty;
            if (pSource.Equals("auto"))
            {
                if (pKey.Equals("guid"))
                    //Create a new GUID
                    pValue = Guid.NewGuid().ToString("B");
                else if (pKey.Equals("now"))
                    //Get current time
                    pValue = DateTime.Now.ToString();
            }
            else if (pSource.Equals("form"))
            {
                if (context.Request[string.Format("{0}{1}", Const.FieldPrefix, pKey)] != null)
                {
                    //Fill the value form request to values
                    pValue = context.Request[string.Format("{0}{1}", Const.FieldPrefix, pKey)];
                }
                else
                {
                    ////If not found
                }
            }
            else if (pSource.Equals("request"))
            {
                if (context.Request[pKey] != null)
                {
                    //Fill the value form request to values
                    pValue = context.Request[pKey];
                }
                else
                {
                    ////If not found
                }
            }
        }


        public int UpdateData(string jsonContent, XmlDocument config)
        {
            return UpdateData(JsonConvert.DeserializeXmlNode(jsonContent), config);
        }

        public int UpdateData(XmlNode doc, XmlNode config)
        {
            XmlNodeList objects = doc.SelectNodes("//Objects/Object");
            int c = 0;
            foreach (XmlNode obj in objects)
            {
                c += UpdateObject(obj, config, null, null);
            }
            return c;
        }

        /// <summary>
        /// Update single object and it's related objects
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="config"></param>
        /// <param name="autoInsert"></param>
        public int UpdateObject(XmlNode obj, XmlNode config, SqlConnection connection, SqlTransaction transaction)
        {
            //isFirstLevel can prevent connection to be closed in a recurcive call
            bool isFirstLevel = connection == null;
            SqlConnection conn = connection;
            SqlTransaction trans = transaction;
            int c = 0;
            try
            {
                //Initialize connection in the first loop
                if (conn == null)
                {
                    conn = new SqlConnection(GetConnectionString(obj, config));
                    conn.Open();
                    if (trans == null && obj.Attributes["isTransaction"] != null && obj.Attributes["isTransaction"].Value.Equals("true"))
                        trans = conn.BeginTransaction();
                }

                //Save master table,which have no child nodes
                XmlNodeList masterProperties = obj.SelectNodes("Property[count(Object) = 0]");
                c += UpdateSingleObject(obj, masterProperties, config, conn, trans);

                //Save detail table
                XmlNodeList detailProperties = obj.SelectNodes("Property[count(Object) > 0]");
                foreach (XmlNode detailProperty in detailProperties)
                {
                    XmlNodeList detailObjects = detailProperty.SelectNodes("Object");
                    foreach (XmlNode detailObject in detailObjects)
                    {
                        //Recurtion here for infinite level object
                        c += UpdateObject(detailObject, config, conn, trans);
                    }
                }

                if (trans != null && isFirstLevel) trans.Commit();
            }
            catch (CustomerException ex)
            {
                if (trans != null && isFirstLevel) trans.Rollback();
                throw ex;
            }
            catch (Exception ex)
            {
                if (trans != null && isFirstLevel) trans.Rollback();
                throw ex;
            }
            finally
            {
                if (isFirstLevel && conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return c;
        }

        public int UpdateSingleObject(XmlNode objectNode, XmlNodeList propNodes, XmlNode config, SqlConnection conn, SqlTransaction trans)
        {
            string sql = "";
            //Justify if need to insert.
            bool insert = false;
            //Insert: table key fields has no existing value
            string sqlCheckItemExist = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}",
                objectNode.Attributes["table"].Value,
                GetKeyEqualClause(objectNode, config));
            int count = (int)DB.ExecuteScalar(conn, trans, CommandType.Text, sqlCheckItemExist, null);
            //If count == 0 then we can insert a new record
            insert = count == 0;

            if (insert)
            {
                //Insert
                StringBuilder sbFields = new StringBuilder();
                StringBuilder sbValues = new StringBuilder();

                foreach (XmlNode prop in propNodes)
                {
                    XmlNode propDefine = GetPropertyNode(config, prop);
                    if (propDefine != null)
                    {
                        //Check the identify wheather should be auto generated
                        if (propDefine.Attributes["key"] != null &&
                            propDefine.Attributes["key"].Value.ToLower().Trim().Equals("true") &&
                            prop.InnerText.Length == 0 &&
                            propDefine.Attributes["key_auto"] != null &&
                            propDefine.Attributes["key_auto"].Value.Equals("true"))
                        {
                            //Auto generate guid key and skip int key
                            if (propDefine.Attributes["key_type"].Value.Equals("guid"))
                            {
                                prop.InnerText = Guid.NewGuid().ToString();
                            }
                            else
                            {
                                continue;
                            }
                        }

                        sbFields.AppendFormat("{0},", prop.Attributes["field"].Value);
                        sbValues.AppendFormat("{0},", GetValueStringByPropertyType(GetPropertyType(propDefine), prop.InnerText));
                    }
                }
                sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                    objectNode.Attributes["table"].Value,
                    sbFields.ToString().Trim(",".ToCharArray()),
                    sbValues.ToString().Trim(",".ToCharArray()));
            }
            else
            {
                //Update
                StringBuilder sbPairs = new StringBuilder();
                foreach (XmlNode prop in propNodes)
                {
                    if (prop.Attributes["field"] != null)
                    {
                        sbPairs.AppendFormat("{0} = {1},", prop.Attributes["field"].Value, GetValueStringByPropertyType(
                            GetPropertyType(prop, config),
                            prop.InnerText));
                    }
                }
                sql = string.Format("UPDATE {0} SET {1} WHERE {2}",
                    objectNode.Attributes["table"].Value,
                    sbPairs.ToString().TrimEnd(",".ToCharArray()),
                    GetKeyEqualClause(objectNode, config));
            }
            //Execute sql statement
            int result = 0;
            try
            {
                result = DB.ExecuteNonQuery(conn, trans, CommandType.Text, sql, null);
            }
            catch(Exception ex)
            {
                throw new CustomerException("Update Object Error. Sql:{0}\r\n Message:{1}\r\n Trace:{2}",sql,ex.Message,ex.StackTrace);
            }
            return result;
        }

        /// <summary>
        /// Delete data from reference object
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        public int DeleteDataByReference(XmlNode objectNode)
        {
            string sql = GetSql(SqlStatementType.Delete, objectNode);
            string connectKey = XmlHelper.GetAttributeValue(objectNode,"connection",Const.DefaultConnectionStringKey);
            return DB.ExecuteNonQuery(DB.GetConnectionString(connectKey),CommandType.Text,sql,null);
        }

        /// <summary>
        /// Delete data
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        public int DeleteDataBySql(XmlNode node)
        {
            if (node == null) return 0;
            XmlNodeList textNodes = node.SelectNodes("Text");
            SqlParameter[] parameterNodes = ParseParameters(node);

            SqlConnection conn = new SqlConnection(DB.GetConnectionString(GetDatabaseConnectionString(node)));
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            int sCount = 0;
            try
            {
                //Delete node can have several text nodes
                foreach (XmlNode textNode in textNodes)
                {
                    sCount += DB.ExecuteNonQuery(conn, trans, CommandType.Text, XmlHelper.GetNodeValue(textNode), parameterNodes);
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                Log.Write(ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return sCount;
        }

        #endregion

        #region Utility Functions
        /// <summary>
        /// Get the predefined connection string
        /// </summary>
        /// <param name="objectNode"></param>
        /// <param name="config"></param>
        /// <returns></returns>
        protected string GetConnectionString(XmlNode objectNode, XmlNode config)
        {
            string connString = DB.ConnectionStringFoundation;
            XmlNode objectDefineNode = config.SelectSingleNode(string.Format("//DataMapping/Object[@table='{0}']", objectNode.Attributes["table"].Value));
            if (objectDefineNode != null && objectDefineNode.ParentNode.Attributes["connString"] != null)
                connString = DB.GetConnectionString(objectDefineNode.ParentNode.Attributes["connString"].Value);
            return connString;
        }

        /// <summary>
        /// Get SQL string by different field type
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        protected string GetValueStringByPropertyType(PropertyType type, string value)
        {
            switch (type)
            {
                case PropertyType.Int:
                    return value;
                case PropertyType.String:
                    return string.Format("'{0}'", value);
                case PropertyType.DataTime:
                    return string.Format("'{0}'", value);
                default:
                    return value;
            }
        }

        /// <summary>
        /// Get the where clause which find the item to be updated
        /// </summary>
        /// <param name="objectNode"></param>
        /// <param name="config"></param>
        /// <returns></returns>
        protected string GetKeyEqualClause(XmlNode objectNode, XmlNode config)
        {
            //Get the predefied object node
            XmlNode objectDefineNode = config.SelectSingleNode(string.Format("//Object[@table='{0}']", objectNode.Attributes["table"].Value));
            if (objectDefineNode != null)
            {
                XmlNodeList keyPropertyNodes = objectDefineNode.SelectNodes("Property[@key='true']");
                StringBuilder sbKeyClause = new StringBuilder();
                if (keyPropertyNodes.Count > 0)
                {
                    //Combine the key properties into a where clause
                    int indexFieldCount = 0;
                    foreach (XmlNode keyPropertyNode in keyPropertyNodes)
                    {
                        //Get the exsiting object key value
                        XmlNode keyNode = objectNode.SelectSingleNode(string.Format("Property[@field='{0}']", keyPropertyNode.Attributes["field"].Value));
                        if (keyNode != null && keyNode.InnerText.Length > 0)
                        {
                            indexFieldCount++;
                            sbKeyClause.AppendFormat("{0} = {1} AND ",
                                keyPropertyNode.Attributes["field"].Value,
                                GetValueStringByPropertyType(GetPropertyType(keyPropertyNode), keyNode.InnerText));
                        }
                        else
                            break;
                    }
                    //If any key property is null or empty which will leed to empty clause
                    if (indexFieldCount < keyPropertyNodes.Count)
                        sbKeyClause.Clear();
                }
                if (sbKeyClause.Length > 0)
                {
                    string clause = sbKeyClause.ToString().Trim();
                    if (clause.EndsWith("AND"))
                        clause = clause.Substring(0, clause.LastIndexOf("AND"));

                    //Verify if clause is empty
                    return clause;
                }
                else
                    return " (1=0) ";
            }
            else
                throw new CustomerException(string.Format("Can not find the Object Node with attribute 'table' equals to {0}", objectNode.Attributes["table"].Value), "Please check the ApplicaitonConfig.xml.");
        }

        /// <summary>
        /// Get Property node from configration node.
        /// </summary>
        /// <param name="config"></param>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        protected XmlNode GetPropertyNode(XmlNode config, string table, string field)
        {
            return config.SelectSingleNode(string.Format(".//Object[@table='{0}']/Property[@field='{1}']", table, field));
        }

        protected XmlNode GetPropertyNode(XmlNode config, XmlNode propNode)
        {
            if (propNode.ParentNode == null)
            {
                throw new CustomerException("Property设置失效:{0}", propNode.OuterXml);
            }
            else
            {
                if (propNode.ParentNode.Attributes["table"] == null)
                    throw new CustomerException("Property父节点无table属性:{0}", propNode.ParentNode.OuterXml);
            }
            if (propNode.Attributes["field"] != null)
                return GetPropertyNode(config, propNode.ParentNode.Attributes["table"].Value, propNode.Attributes["field"].Value);
            else
                return null;
        }

        /// <summary>
        /// Get the predefined property type
        /// </summary>
        /// <param name="propNode"></param>
        /// <param name="config"></param>
        /// <returns></returns>
        protected PropertyType GetPropertyType(XmlNode propNode, XmlNode config)
        {
            XmlNode propDefine = GetPropertyNode(config, propNode);
            if (propDefine != null)
            {
                return GetPropertyType(propDefine);
            }
            else
                return PropertyType.Unknown;
        }

        /// <summary>
        /// Get the property type if you have got an instance of a predefined property node.
        /// </summary>
        /// <param name="propDefine"></param>
        /// <returns></returns>
        private static PropertyType GetPropertyType(XmlNode propDefine)
        {
            if (propDefine.Attributes["type"] == null)
                throw new Exception(string.Format("Can't find type attribute from:{0}", propDefine.OuterXml));
            else
            {
                //Get the property type string
                string typeString = propDefine.Attributes["type"].Value.ToLower().Trim();
                if (typeString.Equals("int"))
                    return PropertyType.Int;
                else if (typeString.Equals("string") || typeString.Equals("varchar"))
                    return PropertyType.String;
                else if (typeString.Equals("datetime"))
                    return PropertyType.DataTime;
                else if (typeString.Equals("relatedfile"))
                    return PropertyType.RelatedFile;
                else if (typeString.Equals("detailtable"))
                    return PropertyType.DetailTable;
                else
                    return PropertyType.Unknown;
            }
        }

        /// <summary>
        /// Get data connection string from a datasource node
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        private string GetDatabaseConnectionString(XmlNode node)
        {
            return XmlHelper.GetAttributeValue(node, "connection", Const.DefaultConnectionStringKey);
        }
        #endregion        

        #region Create Configration

        public string ConnectionKey { get; set; }
        public DatabaseType DatabaseType { get; set; }

        /// <summary>
        /// Get tables from a specified connection key
        /// </summary>
        /// <param name="connectionKey"></param>
        /// <returns></returns>
        public DataSet GetTables()
        {
            if (string.IsNullOrEmpty(ConnectionKey))
            {
                ConnectionKey = Const.DefaultConnectionStringKey;
            }
            StringBuilder sql = new StringBuilder();
            if (DatabaseType == DatabaseType.SQLServer)
            {
                sql.AppendFormat("SELECT * FROM [INFORMATION_SCHEMA].[TABLES] ORDER BY TABLE_NAME");
            }
            if (sql.Length > 0)
                return DB.GetDataSet(DB.GetConnectionString(ConnectionKey), CommandType.Text, sql.ToString(), null);
            else
                return new DataSet();
        }

        /// <summary>
        /// Get all columns by table name
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public DataSet GetColumns(string table)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT a.name COLUMN_NAME, c.name DATA_TYPE,a.isnullable ISNULLABLE, a.length LENGTH, b.value DESCRIPTION  FROM ");
            sql.AppendLine(" systypes c,syscolumns a left join sys.extended_properties b");
            sql.AppendLine(" on a.id=b.major_id AND a.colid = b.minor_id");
            sql.AppendFormat("WHERE a.xusertype = c.xusertype  AND a.id = object_id('{0}')", table); 	
            return DB.GetDataSet(DB.GetConnectionString(ConnectionKey), CommandType.Text, sql.ToString(), null);   
        }

        /// <summary>
        /// Get bizobject node from tables
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="masterTable"></param>
        /// <param name="detailTables"></param>
        /// <returns></returns>
        public XmlNode CreateBizObject(XmlDocument doc,string masterTable, string detailTables)
        {
            if (doc == null)
                throw new CustomerException("XmlDocument is null");
            XmlNode node = doc.CreateElement("Object");
            DataSet dsMasterColumns = GetColumns(masterTable);
            if (DB.HasData(dsMasterColumns))
            {
                AddAttributeToNode(doc, node, "table", masterTable);
                AddAttributeToNode(doc, node, "id", masterTable.ToLower().Replace("_","."));

                //Add master table settings
                foreach (DataRow columnRow in dsMasterColumns.Tables[0].Rows)
                {
                    XmlNode propertyNode = CreateProertyNode(doc, columnRow);
                    node.AppendChild(propertyNode);
                }
                if (!string.IsNullOrEmpty(detailTables))
                {
                    //Add detail table settings
                    foreach (string detailTable in detailTables.Split(",".ToCharArray()))
                    {
                        XmlNode objPropNode = doc.CreateElement("Property");
                        AddAttributeToNode(doc, objPropNode, "name", detailTable);
                        AddAttributeToNode(doc, objPropNode, "title", detailTable);
                        AddAttributeToNode(doc, objPropNode, "type", "object");
                        XmlNode childObjctNode = CreateBizObject(doc, detailTable, null);
                        objPropNode.AppendChild(childObjctNode);
                        //Add to parent object node
                        node.AppendChild(objPropNode);
                    }
                }
            }
            return node;
        }

        /// <summary>
        /// Create a property 
        /// </summary>
        /// <example>
        /// <Property field="SCGJ_CC_CONTENT" type="string" title="xxxx"></Property>
        /// </example>
        /// <param name="doc"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        private XmlNode CreateProertyNode(XmlDocument doc, DataRow row)
        {
            XmlNode propNode = doc.CreateElement("Property");
            string rowDataType = row["DATA_TYPE"].ToString();
            string field = row["COLUMN_NAME"].ToString();
            string title = row["DESCRIPTION"].ToString();
            bool isnull = Convert.ToBoolean(row["ISNULLABLE"]);
            int length = Convert.ToInt32(row["LENGTH"]);

            if (rowDataType.EndsWith("char") || rowDataType.EndsWith("xml") || rowDataType.EndsWith("text"))
            {
                rowDataType = "string";
            }
            else if (rowDataType.EndsWith("int") || rowDataType.EndsWith("float") || rowDataType.EndsWith("double")
                 || rowDataType.EndsWith("real") || rowDataType.EndsWith("money") || rowDataType.EndsWith("bit"))
            {
                rowDataType = "number";
            }
            else if (rowDataType.EndsWith("datetime") || rowDataType.EndsWith("datetime2"))
            {
                rowDataType = "datetime";
            }

            AddAttributeToNode(doc, propNode, "field", field);
            AddAttributeToNode(doc, propNode, "type", rowDataType);
            AddAttributeToNode(doc, propNode, "title", title);
            AddAttributeToNode(doc, propNode, "null", isnull.ToString().ToLower());
            AddAttributeToNode(doc, propNode, "length", length.ToString());

            return propNode;
        }

        public XmlNode CreateApplicationNode(XmlDocument doc, string schema, string title, string masterTable, string detailTables)
        {
            XmlNode appNode = doc.CreateElement("Application");
            XmlNode bizObjectsNode = doc.CreateElement("BizObjects");
            XmlNode bizObjectNode = doc.CreateElement("BizObject");
            XmlNode metaDataNode = doc.CreateElement("MetaData");
            XmlNode dataMappingNode = doc.CreateElement("DataMapping");

            XmlNode objectNode = CreateBizObject(doc, masterTable, detailTables);

            //Set attributes
            AddAttributeToNode(doc,appNode,"xmlns:xsi","http://www.w3.org/2001/XMLSchema-instance");
            //"C:\Projects\Enring\Web\Content\schema\DataObjectSchema.xsd"
            AddAttributeToNode(doc,appNode,"xsi:noNamespaceSchemaLocation",schema);
            AddAttributeToNode(doc, bizObjectNode, "guid", Guid.NewGuid().ToString("B").ToUpper());
            AddAttributeToNode(doc, bizObjectNode, "title", title);

            //Connect Node
            dataMappingNode.AppendChild(objectNode);

            bizObjectNode.AppendChild(metaDataNode);
            bizObjectNode.AppendChild(dataMappingNode);
            bizObjectsNode.AppendChild(bizObjectNode);
            appNode.AppendChild(bizObjectsNode);

            return appNode;
        }

        /// <summary>
        /// Add attribute to a node
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="node"></param>
        /// <param name="attributeName"></param>
        /// <param name="attributeValue"></param>
        public void AddAttributeToNode(XmlDocument doc, XmlNode node, string attributeName, string attributeValue)
        {
            XmlAttribute attr = doc.CreateAttribute(attributeName);
            attr.Value = attributeValue;
            node.Attributes.Append(attr);
        }

        /// <summary>
        /// Createt tables ddl string
        /// </summary>
        /// <param name="tables"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public string CreateDDL(string[] tables,DatabaseType dbType)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string table in tables)
            {
                DataSet ds = GetColumns(table);
                if (DB.HasData(ds))
                {
                    sb.AppendFormat("CREATE TABLE [dbo].[{0}](\r\n", table);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        string rowDataType = row["DATA_TYPE"].ToString();
                        string field = row["COLUMN_NAME"].ToString();
                        bool isnull = Convert.ToBoolean(row["ISNULLABLE"]);
                        int length = Convert.ToInt32(row["LENGTH"]);
                        string lengthDDL = rowDataType.EndsWith("char") && length > 0 ? string.Format("({0})", length) : "";

                        sb.AppendFormat("    [{0}] {1} {2} {3},\r\n",
                            field,
                            rowDataType.ToUpper(),
                            lengthDDL,
                            isnull ? "NULL" : "NOT NULL"
                            );
                    }
                    if (sb.ToString().EndsWith(",\r\n"))
                        sb.Remove(sb.Length - 3, 1);
                    sb.AppendLine(");\r\n\r\n");
                }
            }
            return sb.ToString();
        }


        #endregion
    }
}
